package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IAgencySalaryDao;
import com.boss.salary.entity.AgencySalary;
import com.boss.salary.entity.SalaryHistory;
import com.boss.salary.entity.SalaryMonthEnd;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Repository
public class AgencySalaryDaoImpl extends BaseDaoImpl implements IAgencySalaryDao {

    @Override
    public List<AgencySalary> queryAgencySalaryPage(PaginationDTO paginationDTO) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, agency_id, salary_id")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where  is_deleted=0 ");
        sql.append(" order by create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(null, paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(AgencySalary.class), params);
    }

    @Override
    public int queryAgencySalaryTotal() {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.AGENCY_SALARY);
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveAgencySalary(AgencySalary agencySalary) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_SALARY);
        sql.append("(id, create_time, update_time, salary_id, agency_id, is_accsalwithhold, column_name, is_deleted, mof_div_code) ");
        sql.append("values (?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                agencySalary.getId(), agencySalary.getCreateTime(), agencySalary.getUpdateTime(),
                agencySalary.getSalaryId(), agencySalary.getAgencyId(), agencySalary.getIsAccsalwithhold(),
                agencySalary.getColumnName(), agencySalary.getIsDeleted(), agencySalary.getMofDivCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);

    }

    @Override
    public void updateAgencySalary(AgencySalary agencySalary) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_SALARY);
        sql.append(" set update_time=?, salary_id=?, agency_id=?, is_accsalwithhold=?,")
                .append(" column_name=?, is_deleted=? where id=?");
        Object[] params = new Object[]{
                agencySalary.getUpdateTime(), agencySalary.getSalaryId(), agencySalary.getAgencyId(),
                agencySalary.getIsAccsalwithhold(), agencySalary.getColumnName(), agencySalary.getIsDeleted(),
                agencySalary.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteAgencySalary(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_SALARY);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<AgencySalary> queryAgencySalaryInfo(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, agency_id, salary_id, ")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where is_deleted = '0'");
        if (queryParam.size() > 0) {
            // 单位
            if (!StringUtils.isEmpty(queryParam.get("agencyId"))) {
                String agencyId = queryParam.get("agencyId").toString();
                sql.append(" and agency_id = ? ");
                paramList.add(agencyId);
            }
            //工资
            if (!StringUtils.isEmpty(queryParam.get("salaryId"))) {
                String salaryId = queryParam.get("salaryId").toString();
                sql.append(" and salary_id = ? ");
                paramList.add(salaryId);
            }
            //区划
            if (!StringUtils.isEmpty(queryParam.get("province"))) {
                sql.append(" and mof_div_code = ? ");
                paramList.add(queryParam.get("province"));
            }
        }
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencySalary.class), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectUnitSalaryReportPage(PaginationDTO paginationDTO,
                                                                String agencyId,
                                                                List<String> salaryIds,
                                                                String salaryMonth,
                                                                Map<String, String> currentSalaryIdJoinColumnMap,
                                                                Map<String, Object> condition,
                                                                String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",meritPayCode \"meritPayCode\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\"")
                .append(",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",existsFile \"existsFile\",personnelCode \"personnelCode\", ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder currentStr;
        StringBuilder lastStr;
        if (!CollectionUtils.isEmpty(salaryIds)) {
            currentStr = new StringBuilder();
            lastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            // StringBuilder differentStr = new StringBuilder(" case when  s2 = 0 or "); s2报错!
            StringBuilder differentStr = new StringBuilder(" case when   ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                // String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(" \"").append(currentColumn).append("\", ");
                lastStr.append(currentColumn).append("_history \"").append(currentColumn).append("_history\",");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
                // if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
                // } else {
//                    如果上月没有对应的工资类型，则工资默认为零
                //   ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
                // }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" then 1 else 0 end \"different\" ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId, pet.merit_pay_code meritPayCode, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo, pet.show_order showOrder, pet.personnel_code personnelCode,");
        sb.append(" case when  fio.middle_id is not null and fio.middle_id != '' then 1 else  0 end existsFile ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id  ");
        sb.append(" and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id and smbn.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(" ( select distinct middle_id from ").append(TableName.SAL_FILE_INFO)
                .append(" where ").append(" is_deleted = 0 and agency_id = ? ")
                .append(" and middle_table_code = '2' and salary_month = ? ) fio ")
                .append(" on ").append(" fio.middle_id = pet.person_id ");
        paramList.add(agencyId);
        paramList.add(salaryMonth);
        sb.append(" where ").append(" pet.is_deleted = ?  "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" and  pet.is_stop_salary= 0 ");
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code = ? ");
            paramList.add(mofDivCode);
        }
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
        //concatUnitAuditTotalConditionSql(condition, paramList, sb);
        sb.append("  ) t order by \"different\" desc,  deptId,-showOrder desc  ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);


        return super.getJdbcTemplate().queryForList(querySql, params);
    }


    @Override
    public int selectSalaryChangePersonNumOnSubmit(String agencyId, List<String> salaryIds,
                                                   String salaryMonth,
                                                   String lastSalaryMonth,
                                                   Map<String, String> currentSalaryIdJoinColumnMap,
                                                   Map<String, String> lastSalaryIdJoinColumnMap,
                                                   Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select sum(different) from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" (");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //               String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(", ");
                lastStr.append(currentColumn).append("_history, ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
//                if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
//                } else {
//                    如果上月没有对应的工资类型，则工资默认为零
//                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
//                }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" ) different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.person_id ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" left join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id and pet.agency_id=smed.agency_id ")
                .append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id and pet.agency_id=smbn.agency_id  ")
                .append(" where ")
                .append("  pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");
//        paramList.add(lastSalaryMonth);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if (!CollectionUtils.isEmpty(queryParam)) {
            Object salaryType = queryParam.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ? ");
                paramList.add(String.valueOf(salaryType));
            }
        }

        sb.append(" ) t) d");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public int selectSalaryChangePersonNum(String agencyId, List<String> salaryIds,
                                           String salaryMonth,
                                           String lastSalaryMonth,
                                           Map<String, String> currentSalaryIdJoinColumnMap,
                                           Map<String, String> lastSalaryIdJoinColumnMap,
                                           Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select sum(different) from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" (");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //               String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(", ");
                lastStr.append(currentColumn).append("_history, ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
//                if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
//                } else {
//                    如果上月没有对应的工资类型，则工资默认为零
//                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
//                }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" ) different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.person_id ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id and pet.agency_id=smed.agency_id ")
                .append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id and pet.agency_id=smbn.agency_id  ")
                .append(" where ")
                .append("  pet.is_deleted  = 0 ")
                .append(" and pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");
//        paramList.add(lastSalaryMonth);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if (!CollectionUtils.isEmpty(queryParam)) {
            Object salaryType = queryParam.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ? ");
                paramList.add(String.valueOf(salaryType));
            }
        }

        sb.append(" ) t) d");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public List<Map<String, Object>> selectUnitAuditPage(PaginationDTO paginationDTO,
                                                         String agencyId,
                                                         List<String> salaryIds,
                                                         String salaryMonth,
                                                         Map<String, String> currentSalaryIdJoinColumnMap,
                                                         Map<String, Object> condition, String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",isThirteen \"isThirteen\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" , case when  ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn).append(" \"").append(currentColumn).append("\"");
                lastStr.append(" , ").append(currentColumn).append("_history ").append(" \"").append(currentColumn).append("_history\" ");
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
                //if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" , ").append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history ");
                //} else {
//                    如果上月没有对应的工资类型，则工资默认为零
                //  ifNullLastStr.append(" , 0 ").append(currentColumn).append("_history ");
                // }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                differentStr.append(" then 1 else 0 end different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, nvl(smed.is_thirteen, 0) isThirteen, pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id= smed.person_id  and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and is_thirteen = 0 ")
                .append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id  and smbn.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 ");
        sb.append(" and pet.is_stop_salary= 0 ");
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        sb.append("   and pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
//        拼接第十三月工资
//        sb.append(" union all ");
//        concatThirteenSalary(sb, paramList, agencyId, salaryIds,
//                currentSalaryIdJoinColumnMap, condition,mofDivCode);

        sb.append(" ) t order by isThirteen ,updateTime desc ");//, different desc,
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }


    private void concatThirteenSalary(StringBuilder sb,
                                      List<Object> paramList,
                                      String agencyId,
                                      List<String> salaryIds,
                                      Map<String, String> currentSalaryIdJoinColumnMap,
                                      Map<String, Object> condition, String mofDivCode) {
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" '13月' currentMonth, '' currentMonth_history, nvl(smed.is_thirteen, 0) isThirteen, ")
                .append(" pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        for (String salaryId : salaryIds) {
            String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
            ifNullCurrentStr.append(" , nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
            ifNullLastStr.append(" , 0 ").append(currentColumn).append("_history ");
        }
        sb.append(ifNullCurrentStr).append(ifNullLastStr);
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 1 ");
        paramList.add(agencyId);
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pio.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditPageConditionSql(sb, paramList, condition);
    }

    private void concatUnitAuditPageConditionSql(StringBuilder sb, List<Object> paramList, Map<String, Object> condition) {
        if (!CollectionUtils.isEmpty(condition)) {
            Object state = condition.get("state");
            if (!StringUtils.isEmpty(state)) {
                sb.append(" and pet.state = ? ");
                paramList.add(String.valueOf(state));
            }
            Object personName = condition.get("personName");
            if (!StringUtils.isEmpty(personName)) {
                sb.append(" and pet.person_name like concat('%',?,'%') ");
                paramList.add(String.valueOf(personName));
            }

            Object idenNo = condition.get("idenNo");
            if (!StringUtils.isEmpty(idenNo)) {
                sb.append(" and pet.iden_no like concat('%',?,'%') ");
                paramList.add(String.valueOf(idenNo));
            }
            Object salaryType = condition.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ?");
                paramList.add(String.valueOf(salaryType));
            }
            Object deptId = condition.get("deptId");
            if (!StringUtils.isEmpty(deptId)) {
                sb.append(" and pet.dept_id = ?");
                paramList.add(String.valueOf(deptId));
            }
            Object personnelCode = condition.get("personnelCode");
            if (!StringUtils.isEmpty(personnelCode)) {
                sb.append(" and pet.personnel_code like concat('%',?,'%') ");
                paramList.add(String.valueOf(personnelCode));
            }

            //按工资项过滤金额
            Object salaryColum = condition.get("salaryColum");
            Object salaryColumMin = condition.get("salaryColumMin");
            Object salaryColumMax = condition.get("salaryColumMax");
            if (!StringUtils.isEmpty(salaryColum) && (!StringUtils.isEmpty(salaryColumMax) || !StringUtils.isEmpty(salaryColumMin))) {

                if (!StringUtils.isEmpty(salaryColumMax)) {//小于等于最大值
                    sb.append(" and smed.").append(salaryColum);
                    sb.append(" <= ?");
                    paramList.add(String.valueOf(salaryColumMax));
                }
                if (!StringUtils.isEmpty(salaryColumMin)) {//大于等于最小值
                    sb.append(" and smed.").append(salaryColum);
                    sb.append(" >= ?");
                    paramList.add(String.valueOf(salaryColumMin));
                }
            }
        }
    }

    @Override
    public SalaryHistory selectSalaryHistoryDataByPersonId(String agencyId,
                                                           String personId,
                                                           String lastSalaryMonth,
                                                           List<String> queryColumn) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        StringBuilder sb = new StringBuilder();
        sb.append(" select id");
        for (String name : queryColumn) {
            sb.append(",").append(" nvl(").append(name).append(", 0) ").append(name);
        }
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_BEGIN)
                .append(" where ")
                .append(" agency_id = ? ")
                .append(" and person_id = ? ");
        //  .append(" and salary_month = ? ");
        try {
            return super.getJdbcTemplate().queryForObject(
                    sb.toString(), new BeanPropertyRowMapper<>(SalaryHistory.class), agencyId, personId);
        } catch (EmptyResultDataAccessException e) {
//            查询结果为空会报这个错，所以在报错的时候catch并返回null
            SalaryHistory salaryHistory = new SalaryHistory();
            Class<?> clazz = salaryHistory.getClass();
            for (String name : queryColumn) {
                Method setMethod = clazz.getMethod("set" + name.substring(0, 1).toUpperCase() + name.substring(1), Double.class);
                setMethod.invoke(salaryHistory, 0.00);
            }
            return salaryHistory;
        }
    }

    @Override
    public int selectUnitSalaryReportTotal(String agencyId, Map<String, Object> condition, String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ");

        if (!StringUtils.isEmpty(condition.get("salaryColum"))
                && (!StringUtils.isEmpty(condition.get("salaryColumMax"))
                || !StringUtils.isEmpty(condition.get("salaryColumMin")))) {
            sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                    .append(" on ").append(" pet.person_id = smed.person_id ");
            sb.append(" and smed.agency_id = ? ");
            paramList.add(agencyId);
        }
        sb.append(" where ").append(" pet.is_deleted = ? "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.is_stop_salary = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and  pet.is_stop_salary = ?  "); // 过滤掉已停发的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code = ?");
            paramList.add(mofDivCode);
        }

        concatUnitAuditPageConditionSql(sb, paramList, condition);
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public int selectUnitAuditTotal(String agencyId, Map<String, Object> condition, String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (")
                .append(" select pio.id ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        sb.append(" and pet.is_stop_salary=0 ");
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pio.mof_div_code=?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditTotalConditionSql(condition, paramList, sb);

        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    private void concatUnitAuditTotalConditionSql(Map<String, Object> condition, List<Object> paramList, StringBuilder sb) {
        if (!CollectionUtils.isEmpty(condition)) {
            Object personId = condition.get("personId");
            if (!StringUtils.isEmpty(personId)) {
                sb.append(" and pet.person_id = ? ");
                paramList.add(String.valueOf(personId));
            }

            Object state = condition.get("state");
            if (!StringUtils.isEmpty(state)) {
                sb.append(" and pet.state = ? ");
                paramList.add(String.valueOf(state));
            }
            Object personName = condition.get("personName");
            if (!StringUtils.isEmpty(personName)) {
                sb.append(" and pet.person_name like concat('%',?,'%') ");
                paramList.add(String.valueOf(personName));
            }

            Object idenNo = condition.get("idenNo");
            if (!StringUtils.isEmpty(idenNo)) {
                sb.append(" and pet.iden_no like concat('%',?,'%') ");
                paramList.add(String.valueOf(idenNo));
            }
            Object salaryType = condition.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ?");
                paramList.add(String.valueOf(salaryType));
            }
            Object deptId = condition.get("deptId");
            if (!StringUtils.isEmpty(deptId)) {
                sb.append(" and pet.dept_id = ?");
                paramList.add(String.valueOf(deptId));
            }


        }
    }

    @Override
    public Map<String, Object> selectUnitSalarySubmitReportSum(String agencyId, List<String> salaryIds, String salaryMonth, Map<String, String> currentSalaryIdJoinColumnMap, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select '合计' \"personName\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , sum(").append(currentColumn).append(") ").append(currentColumn);
                ifNullCurrentStr.append(" , ").append(" sum(nvl( smed.").append(currentColumn).append(", 0 )) ").append(currentColumn);
            }
            sb.append(currentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time \"updateTime\", pio.id \"personId\", pet.agency_id \"agencyId\", ")
                .append(" ? \"currentMonth\", ");
        paramList.add(salaryMonth);
        sb.append(" nvl(smed.is_thirteen, 0) \"isThirteen\", pet.dept_id \"deptId\", pio.person_name \"personName\", pio.iden_no \"idenNo\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and pet.is_stop_salary = 0 and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and is_thirteen = 0 ");
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.agency_id = ? ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if (!StringUtils.isEmpty(queryParam.get("mofDivCode"))) {
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        concatReportPageConditionSql(sb, paramList, queryParam);
        sb.append(" GROUP BY smed.id," +
                "    smed.update_time ," +
                "    pio.id ," +
                "    pet.agency_id ," +
                "    ? ," +
                "    nvl(smed.is_thirteen, 0) ," +
                "    pet.dept_id ," +
                "    pio.person_name ," +
                "    pio.iden_no ");
        paramList.add(salaryMonth);
        sb.append(" ) t order by isThirteen, updateTime desc ");
        return super.getJdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectUnitSalarySubmitReportPage(PaginationDTO paginationDTO, String agencyId, List<String> salaryIds,
                                                                      String salaryMonth, Map<String, String> currentSalaryIdJoinColumnMap,
                                                                      Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select @i:=@i+1 no, ")
                .append(" id, updateTime, personId, agencyId, currentMonth, deptId, personName, idenNo, isThirteen ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn);
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
            }
            sb.append(currentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" nvl(smed.is_thirteen, 0) isThirteen, pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and pet.is_stop_salary = 0 and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and is_thirteen = 0 ");
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.agency_id = ? ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if (!StringUtils.isEmpty(queryParam.get("mofDivCode"))) {
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
//        paramList.add(agencyId);
        concatReportPageConditionSql(sb, paramList, queryParam);
//        拼接第十三月工资
//        sb.append(" union all ");
//        concatReportThirteenSalary(sb, paramList, agencyId, salaryIds,
//                currentSalaryIdJoinColumnMap, queryParam);

        sb.append(" ) t, (select   @i:=0) v order by isThirteen, updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectUnitSalarySubmitReportTotal(String agencyId, String lastSalaryMonth, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (");
        sb.append(" select smed.person_id id ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 0 ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if (!StringUtils.isEmpty(queryParam.get("mofDivCode"))) {
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        concatReportPageConditionSql(sb, paramList, queryParam);
        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public int selectHomeUnitSalaryReportTotal(String agencyId, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (");
        sb.append(" select smed.person_id id ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 0 ")
                .append(" and av.salary_verified_in_agency_code = ?")
                .append(" and (av.is_salary_submit = '0' or av.is_salary_submit = '-9' or av.is_salary_submit = '-1')");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }


    private void concatReportThirteenSalary(StringBuilder sb,
                                            List<Object> paramList,
                                            String agencyId,
                                            List<String> salaryIds,
                                            Map<String, String> currentSalaryIdJoinColumnMap,
                                            Map<String, Object> condition) {
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" '13月' currentMonth, nvl(smed.is_thirteen, 0) isThirteen, ")
                .append(" pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        for (String salaryId : salaryIds) {
            String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
            ifNullCurrentStr.append(" , nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
        }
        sb.append(ifNullCurrentStr).append(ifNullLastStr);
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 1 ");
        paramList.add(agencyId);
        concatReportPageConditionSql(sb, paramList, condition);
    }

    private void concatReportPageConditionSql(StringBuilder sql, List<Object> paramList, Map<String, Object> queryParam) {
        if (!CollectionUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if ("0".equals(queryParam.get("flag"))) {
                    // 未上报和撤销
                    sql.append(" and (av.is_salary_submit = '0' or av.is_salary_submit = '-9')");
                }
            }
            // 变动情况
            if (!StringUtils.isEmpty(queryParam.get("changes"))) {
                if (queryParam.get("changes").equals("0")) {
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            // 工资类型
            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
                sql.append(" and pet.merit_pay_code = ? ");
                paramList.add(queryParam.get("salaryType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and pio.state = ? ");
                paramList.add(queryParam.get("personType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                sql.append(" and pio.person_name like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("personName"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and pio.iden_no like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("idenNo"));
            }
        }
    }


    @Override
    public void physicsDeleteAgencySalary(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();

        sql.append(" delete from ").append(TableName.AGENCY_SALARY);
        sql.append(" where 1 = 1");
        if (!StringUtils.isEmpty(queryParam) && queryParam.size() > 0) {
            // 包含单位信息，根据单位id删除
            if (queryParam.containsKey("agencyId")) {
                String agencyId = queryParam.get("agencyId").toString();
                sql.append(" and agency_id = '").append(agencyId).append("'");
            }
            // 包含工资信息，根据工资id删除
            if (queryParam.containsKey("salaryId")) {
                String salaryId = queryParam.get("salaryId").toString();
                sql.append(" and salary_id = '").append(salaryId).append("'");
            }
        }
        this.getJdbcTemplate().update(sql.toString());
    }

    @Override
    public Map<String, Object> selectSalaryMonthEndSum(String agencyId,
                                                       Integer queryThirteen,
                                                       List<String> queryColumn,
                                                       Map<String, Object> condition, String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String name : queryColumn) {
            sb.append(" nvl(sum(").append(name).append("), 0.00) ").append(name);
            if (!(queryColumn.indexOf(name) == queryColumn.size() - 1)) {
                sb.append(", ");
            }
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" AND pet.is_stop_salary = 0 ")
                .append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        if (SystemDictConstant.IS_FALSE.equals(queryThirteen)) {
            sb.append("  and smed.is_thirteen = 0 ");
        }

        sb.append(" where ").append(" pio.is_deleted = 0  "); // 过滤掉已删除的用户
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pio.mof_div_code= ? ");
            paramList.add(mofDivCode);
        }

        concatUnitAuditPageConditionSql(sb, paramList, condition);
        if (!StringUtils.isEmpty(condition.get("salaCardBank"))) {
            sb.append(" and pet.sala_card_bank = ?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getjdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectSalaryName(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" sba.salary_name \"key\", asy.column_name \"value\" , is_compute \"iscompute\" ")
                .append(" from ")
                .append(TableName.AGENCY_SALARY).append(" asy ")
                .append(" left join ").append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" on ").append(" asy.salary_id = sba.id ")
                .append(" where ")
                .append(" sba.is_deleted = 0 and sba.base_state = '1' ")
                .append(" and asy.is_deleted = 0 and asy.agency_id =  ? ")
                .append(" order by sba.show_order ");
        return getjdbcTemplate().queryForList(sb.toString(), agencyId);
    }

    @Override
    public List<AgencySalaryVO> selectColumnName(UserDTO userDTO) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" sba.id \"salaryId\", ")
                .append(" sba.salary_code \"columnName\", ")
                .append(" sba.is_compute \"isCompute\", ")
                .append(" sba.salary_name \"salaryName\"")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" where ")
                .append(" sba.is_deleted = 0 and sba.base_state = '1' ")
                .append(" and  sba.mof_div_code=? ")
                .append(" order by sba.show_order ");
        return getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(AgencySalaryVO.class), userDTO.getProvince());

    }

    @Override
    public List<AgencySalaryVO> selectColumnName(UserDTO userDTO, List<String> salaryCodeList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" sba.salary_code, sba.salary_name,")
                .append(" sba.salary_code columnName ")
//                .append(" nvl(asy.column_name, 0) columnName ")
                .append(" from ").append(TableName.SALARY_BASEDATA).append(" sba ");
//                .append(" left join ").append(TableName.AGENCY_SALARY).append(" asy ")
//                .append(" on ").append(" asy.salary_id = sba.id and asy.is_deleted = 0 and asy.agency_id = ? ");
//        params.add(agencyId);
        sb.append(" where ").append(" sba.is_deleted = 0 and sba.base_state = '1' ").append(" and  sba.mof_div_code=? ");
        params.add(userDTO.getProvince());
        StringBuilder inSql = new StringBuilder();
        StringBuilder orderSql = new StringBuilder();
        if (!CollectionUtils.isEmpty(salaryCodeList)) {
            inSql.append(" and sba.salary_code in ( ");
            orderSql.append(" order by field(sba.salary_code, ");
            for (String salaryCode : salaryCodeList) {
                if (salaryCodeList.indexOf(salaryCode) != salaryCodeList.size() - 1) {
                    inSql.append(" ?, ");
                    orderSql.append(" ?, ");
                } else {
                    inSql.append(" ?) ");
                    orderSql.append(" ?) ");
                }
            }
            sb.append(inSql);
            params.addAll(salaryCodeList);
            sb.append(orderSql);
            params.addAll(salaryCodeList);
        }
        return super.getjdbcTemplate().query(sb.toString(), params.toArray(), new BeanPropertyRowMapper<>(AgencySalaryVO.class));
    }


    @Override
    public List<AgencySalaryVO> selectLastColumnName(String agencyId, List<String> salaryIds, String lastSalaryMonth) {
        if (StringUtils.isEmpty(agencyId) || CollectionUtils.isEmpty(salaryIds)) {
            return Collections.emptyList();
        }
        StringBuilder sb = new StringBuilder();
        List<String> params = new ArrayList<>();
        sb.append(" select ")
                .append(" salary_id, column_name ")
                .append(" from ")
                .append(TableName.AGENCY_SALARY_HISTORY)
                .append(" where ")
                .append(" is_deleted = 0 ")
                .append(" and salary_month = ? ")
                .append(" and agency_id = ? ");
        params.add(lastSalaryMonth);
        params.add(agencyId);
        sb.append(" and salary_id in(");
        for (String salaryId : salaryIds) {
            sb.append(" ? ");
            params.add(salaryId);
            if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                sb.append(" ) ");
            } else {
                sb.append(" , ");
            }

        }
        return getjdbcTemplate().query(sb.toString(), params.toArray(), new BeanPropertyRowMapper<>(AgencySalaryVO.class));
    }

    @Override
    public String selectReallySendSalaryColumnName(String agencyId) {
        String reallySendSalaryId = "46F1370C5F2A4D31AD196718A93DC470";
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" salary_code ")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where ")
                .append(" is_deleted = 0  and salary_id = ? ");
        try {
            return super.getjdbcTemplate().queryForObject(sb.toString(), String.class, reallySendSalaryId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<AgencySalary> getInfoByAgencyIds(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id, create_time, update_time, agency_id, salary_id, ")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencySalary.class));
    }

    @Override
    public void batchSaveAgencySalaryHistory(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.AGENCY_SALARY_HISTORY)
                .append(" ( id, create_time, update_time, salary_month, agency_id, salary_id, is_accsalwithhold, ")
                .append(" column_name, is_deleted,mof_div_code )")
                .append(" values (")
                .append("?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }

            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Object[] sme = params.get(i);
                for (int a = 0; a < 10; a++) {
                    ps.setObject(a + 1, sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteAgencySalaryHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.AGENCY_SALARY_HISTORY);
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{month});
    }

    @Override
    public List<SalaryMonthEnd> getSalaryMonthByPersonId(List<String> columnName, String agencyId, String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id");
        for (String column : columnName) {
            sql.append(" ,").append(column);
        }
        sql.append(" from ").append(TableName.SALARY_MONTH_END);
        sql.append(" where person_id = ?");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class), new Object[]{personId});
    }

    @Override
    public List<SalaryMonthEnd> getSalaryMonthEedSubmitByPersonId(List<String> columnName, String agencyId, String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id");
        for (String column : columnName) {
            sql.append(" ,").append(column);
        }
        sql.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT);
        sql.append(" where person_id = ?");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class), new Object[]{personId});
    }

    @Override
    public List<Map<String, Object>> getSelectUnitSalaryReport(String agencyId, List<String> salaryIds, String salaryMonth, String lastSalaryMonth, Map<String, String> currentSalaryIdJoinColumnMap, Map<String, String> lastSalaryIdJoinColumnMap, Map<String, Object> condition) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id, updateTime, personId, agencyId, currentMonth, currentMonth_history, deptId, personName, idenNo, ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder currentStr;
        StringBuilder lastStr;
        if (!CollectionUtils.isEmpty(salaryIds)) {
            currentStr = new StringBuilder();
            lastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" (");
//            for (String salaryId : salaryIds) {
//                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
//                //String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
//                currentStr.append(currentColumn).append(", ");
//                lastStr.append(currentColumn).append("_history, ");
//                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
////                if (!StringUtils.isEmpty(lastColumn)) {
//                    ifNullLastStr.append(" nvl( shy.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
////                } else {
//////                    如果上月没有对应的工资类型，则工资默认为零
////                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
////                }
//                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
////                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
////                    differentStr.append(" ) different ");
////                } else {
////                    differentStr.append(" or ");
////                }
//            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, ");
        paramList.add(lastSalaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.is_stop_salary = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.is_thirteen = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_HISTORY).append(" shy ")
                .append(" on ").append(" pio.id = shy.person_id ")
                .append(" and shy.salary_month = ? ");
        paramList.add(lastSalaryMonth);
        sb.append(" and shy.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = ? "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        concatUnitAuditTotalConditionSql(condition, paramList, sb);
        sb.append(" ) t order by updateTime desc ");//different desc,

        return super.getJdbcTemplate().queryForList(sb.toString(), paramList.toArray());


    }


    @Override
    public List<Map<String, Object>> getFinancialAuditSalaryPage(PaginationDTO paginationDTO,
                                                                 String agencyId,
                                                                 List<String> salaryIds,
                                                                 String salaryMonth,
                                                                 String lastSalaryMonth,
                                                                 Map<String, String> currentSalaryIdJoinColumnMap,
                                                                 Map<String, Object> condition, String mofDivCode,
                                                                 String flag) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id, updateTime, personId, agencyId, currentMonth, currentMonth_history, deptId, personName, idenNo, isThirteen ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" , ( s2 = 0 or ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn);
                lastStr.append(" , ").append(currentColumn).append("_history ");
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
                ifNullLastStr.append(" , ").append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history ");
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" ) different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId,show_order, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, nvl(smed.is_thirteen, 0) isThirteen, pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        if (!"-2".equals(flag)) {
            sb.append(" from ")
                    .append(TableName.PERSON_EXT_SUBMIT).append(" pet ");
            sb.append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ");
        } else {
            sb.append(" from ")
                    .append(TableName.PERSON_EXT).append(" pet ");
            sb.append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ");
        }
        sb.append(" on ").append(" pet.person_id = smed.person_id  and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id  and smbn.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 ");
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        sb.append("  and pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);

        sb.append(" ) t order by  different desc, -show_order desc ,idenNo ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectFinancialAuditSalaryTotal(String agencyId, String lastSalaryMonth, Map<String, Object> condition, String mofDivCode, String flag) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ")
                .append(" from ");
        if (!"-2".equals(flag)) {//不是已退回页签查询上报表
            sb.append(TableName.PERSON_EXT_SUBMIT);
        } else {//是已退回页签查询单位表
            sb.append(TableName.PERSON_EXT);
        }
        sb.append(" pet ");
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 and pet.is_deleted = 0 and pet.agency_id = ?  ");
        paramList.add(agencyId);
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code=?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditTotalConditionSql(condition, paramList, sb);
//        sb.append(" union all ");
//        sb.append(" select smed.person_id id ")
//                .append(" from ")
//                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
//                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
//                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
//                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
//                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
//                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
//        paramList.add(agencyId);
//        sb.append(" where ").append(" smed.agency_id = ? ");
//        paramList.add(agencyId);
//        if(!StringUtils.isEmpty(mofDivCode)){
//            sb.append(" and pio.mof_div_code=?");
//            paramList.add(mofDivCode);
//        }
//        concatUnitAuditTotalConditionSql(condition, paramList, sb);
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }


    @Override
    public Map<String, Object> selectSalaryMonthEndSubmSum(String agencyId,
                                                           Integer queryThirteen,
                                                           List<String> queryColumn,
                                                           Map<String, Object> condition, String mofDivCode, String flag) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String name : queryColumn) {
            sb.append(" nvl(sum(").append(name).append("), 0.00) ").append(name);
            if (!(queryColumn.indexOf(name) == queryColumn.size() - 1)) {
                sb.append(", ");
            }
        }
        sb.append(" from ");
        if (!"-2".equals(flag)) {
            sb.append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                    .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ");
        } else {
            sb.append(TableName.PERSON_EXT).append(" pet ")
                    .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ");
        }
        sb.append(" on ").append(" pet.person_id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        if (SystemDictConstant.IS_FALSE.equals(queryThirteen)) {
            sb.append("  and smed.is_thirteen = 0 ");
        }

        sb.append(" where ").append(" pet.is_deleted = 0  "); // 过滤掉已删除的用户
        if (!StringUtils.isEmpty(mofDivCode)) {
            sb.append(" and pet.mof_div_code= ? ");
            paramList.add(mofDivCode);
        }
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
        if (!StringUtils.isEmpty(condition.get("salaCardBank"))) {
            sb.append(" and pet.sala_card_bank = ?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getjdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }
}
